#include <sqlite3.h>
#include <stdlib.h>
#include <stdio.h>
#include <string.h>

void createdb() {
    int                     ret;
    sqlite3               *pdb = 0;
    sqlite3_stmt      *stmt = 0;
    char   *error = 0;
    const char    *sql = "insert into table1 values('value11',:aaa)";
    int         index;
    static const char *value = "asdfadsfasdfjasdfjaksdfaskjdfakdsfaksfja";

    ret = sqlite3_open("db1.sdb", &pdb);                    //打开数据库，跟打开文本文件一样
    if( ret != SQLITE_OK )
        return;
    ret = sqlite3_exec(pdb, "create table table1(col1 char(20), col2 BLOB)", 0,0, &error );
    if( ret != SQLITE_OK )
        return;
    const char* pzTail = NULL;
    ret = sqlite3_prepare(pdb, sql,strlen(sql), &stmt, &pzTail);
    if( ret != SQLITE_OK )
        return;

    index = sqlite3_bind_parameter_index(stmt, ":aaa");
    ret = sqlite3_bind_blob(stmt, index, (void*)value, strlen(value), SQLITE_STATIC);
    if( ret != SQLITE_OK )
        return;
    ret = sqlite3_step(stmt);
    if( ret != SQLITE_DONE )
        return;
    sqlite3_close(pdb);
}

void querydb()
{
    int                     ret;
    sqlite3   *pdb = 0;
    sqlite3_stmt      *pstmt = 0;
    const char *error = 0;
    const char *sql = "select * from table1";
    int                     len;
    char      *name;
    void       *value;

    ret = sqlite3_open("db1.sdb", &pdb);
    if( ret != SQLITE_OK )
        return;

    ret = sqlite3_prepare(pdb, sql, strlen(sql), &pstmt, &error);
    if( ret != SQLITE_OK )
        return;

    int nCount = sqlite3_column_count(pstmt);

    if(pstmt) {
        while(sqlite3_step(pstmt) == SQLITE_ROW) {
            name = (char *)sqlite3_column_text(pstmt, 0);
            value = (char*)sqlite3_column_blob(pstmt, 1);
            const char *cname1 = sqlite3_column_name(pstmt, 0);
            const char *cname2 = sqlite3_column_name(pstmt, 1);
            len = sqlite3_column_bytes(pstmt,1 );
            printf("name=%s, value=%s, vlen=%d nCount=%d, c1=%s, c2=%s\n", name, value, len, nCount , cname1, cname2);
        }
        sqlite3_finalize(pstmt);
    }
}

int main() {
    createdb();
    querydb();
    return 0;
}

